package com.xjx.programmer.service;

import com.xjx.programmer.model.DatabaseConnection;
import com.xjx.programmer.model.ExecuteResult;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;

import javax.sql.DataSource;
import java.sql.*;
import java.util.*;

/**
 * PostgreSQL 数据库服务
 */
@Slf4j
@Service
public class PostgreSqlService {

    /**
     * 创建 PostgreSQL 数据源
     */
    public DataSource createDataSource(DatabaseConnection connection) {
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl(connection.getUrl());
        config.setUsername(connection.getUsername());
        config.setPassword(connection.getPassword());
        config.setDriverClassName("org.postgresql.Driver");
        config.setMaximumPoolSize(10);
        config.setMinimumIdle( 5);
        config.setConnectionTimeout(30000);
        config.setIdleTimeout(600000);
        config.setMaxLifetime(1800000);
        
        return new HikariDataSource(config);
    }

    /**
     * 执行 SQL 语句
     */
    public ExecuteResult executeSql(DataSource dataSource, String sql) {
        long startTime = System.currentTimeMillis();
        
        try (Connection connection = dataSource.getConnection();
             Statement statement = connection.createStatement()) {
            
            // 判断是否为查询语句
            String trimmedSql = sql.trim().toLowerCase();
            boolean isQuery = trimmedSql.startsWith("select") || 
                             trimmedSql.startsWith("show") || 
                             trimmedSql.startsWith("describe") || 
                             trimmedSql.startsWith("explain") ||
                             trimmedSql.startsWith("with");
            
            if (isQuery) {
                return executeQuery(statement, sql, startTime);
            } else {
                return executeUpdate(statement, sql, startTime);
            }
            
        } catch (SQLException e) {
            log.error("执行 SQL 失败: {}", sql, e);
            return ExecuteResult.builder()
                    .success(false)
                    .message("执行失败")
                    .executeTime(System.currentTimeMillis() - startTime)
                    .errorMessage(e.getMessage())
                    .errorStack(Arrays.toString(e.getStackTrace()))
                    .build();
        }
    }

    /**
     * 执行查询语句
     */
    private ExecuteResult executeQuery(Statement statement, String sql, long startTime) throws SQLException {
        List<Map<String, Object>> data = new ArrayList<>();
        
        try (ResultSet resultSet = statement.executeQuery(sql)) {
            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnCount = metaData.getColumnCount();
            
            while (resultSet.next()) {
                Map<String, Object> row = new HashMap<>();
                for (int i = 1; i <= columnCount; i++) {
                    String columnName = metaData.getColumnName(i);
                    Object value = resultSet.getObject(i);
                    row.put(columnName, value);
                }
                data.add(row);
            }
        }
        
        return ExecuteResult.builder()
                .success(true)
                .message("查询成功")
                .executeTime(System.currentTimeMillis() - startTime)
                .data(data)
                .build();
    }

    /**
     * 执行更新语句
     */
    private ExecuteResult executeUpdate(Statement statement, String sql, long startTime) throws SQLException {
        int affectedRows = statement.executeUpdate(sql);
        
        return ExecuteResult.builder()
                .success(true)
                .message("执行成功")
                .executeTime(System.currentTimeMillis() - startTime)
                .affectedRows(affectedRows)
                .build();
    }

    /**
     * 测试数据库连接
     */
    public boolean testConnection(DatabaseConnection connection) {
        DataSource dataSource = null;
        try {
            dataSource = createDataSource(connection);
            try (Connection conn = dataSource.getConnection()) {
                return conn.isValid(5);
            }
        } catch (SQLException e) {
            log.error("测试 PostgreSQL 连接失败", e);
            return false;
        } finally {
            // 关闭数据源
            if (dataSource instanceof HikariDataSource) {
                ((HikariDataSource) dataSource).close();
            }
        }
    }
}
